﻿Imports System.Data.OleDb
Public Class VideoDetail
    Inherits System.Web.UI.Page


    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If IsPostBack = False Then   'we have come here from another page
            If Request.Params("VideoID") = "" Then Response.Redirect("Artists.aspx") 'if no params in ID, then back to start
            'create a data reader field
            Dim oleDbConn As New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("3dBuzzDatabase").ConnectionString)
            Dim SqlString As String = "SELECT * FROM Comments WHERE ([Showreel_fkId] = @f1)"
            Dim cmd As OleDbCommand = New OleDbCommand(SqlString, oleDbConn)
            cmd.CommandType = CommandType.Text
            cmd.Parameters.AddWithValue("@f1", Request.Params("VideoId"))
            oleDbConn.Open()
            'this is the data reader
            Dim dataReader = cmd.ExecuteReader()    'because we are returning results
            GridView3.DataSource = dataReader
            GridView3.DataBind()
            lbl_UserName.Text = User.Identity.Name

            Dim findIdSqlString3 As String = "SELECT  aspnet_Users.[UserId] FROM aspnet_Users WHERE aspnet_Users.UserName = @f33"
            Dim findIdCmd3 As New OleDbCommand(findIdSqlString3, oleDbConn)
            findIdCmd3.Parameters.AddWithValue("@f33", User.Identity.Name)
            findIdCmd3.ExecuteNonQuery()
            Dim dataReader3 As OleDbDataReader = findIdCmd3.ExecuteReader()
            Dim storeid3 As Integer

            dataReader3.Read()
            storeid3 = dataReader3.Item("UserId")
            Dim storeida3 As String = storeid3.ToString


            Dim findLikesSqlString As String = "SELECT * FROM Likes WHERE [VideoId_fk] = @f30 AND [UserId_fk] = @f31"
            Dim findLikesCmd As OleDbCommand = New OleDbCommand(findLikesSqlString, oleDbConn)
            findLikesCmd.Parameters.AddWithValue("@f30", Request.Params("VideoId"))
            findLikesCmd.Parameters.AddWithValue("@f31", storeida3)

            Dim dataReader4 As OleDbDataReader = findLikesCmd.ExecuteReader()
            If dataReader4.HasRows Then
                btn_like.Visible = False
                lbl_like.Visible = True
            Else
                btn_like.Visible = True
                lbl_like.Visible = False
            End If



        End If

    End Sub

    Protected Sub GridView1_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs) Handles GridView2.SelectedIndexChanged

    End Sub

    Protected Sub btn_AddComment_Click(sender As Object, e As EventArgs) Handles btn_AddComment.Click
        Dim oleDbConn As New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("3dBuzzDatabase").ConnectionString)
        Dim SqlString As String = "INSERT into Comments( Author, Title, Comment, Showreel_fkId ) Values( @f1, @f2, @f3, @f4)"
        Dim cmd As OleDbCommand = New OleDbCommand(SqlString, oleDbConn)
        cmd.CommandType = CommandType.Text
        cmd.Parameters.AddWithValue("@f1", User.Identity.Name)
        cmd.Parameters.AddWithValue("@f2", tb_Title.Text)
        cmd.Parameters.AddWithValue("@f3", tb_Comment.Text)
        cmd.Parameters.AddWithValue("@f4", Request.Params("VideoId"))
        oleDbConn.Open()
        cmd.ExecuteNonQuery()

        '' fill GridView3 if we have submitted the page ourselves (eg after user has added comments)

        Dim fillComments As String = "SELECT * FROM Comments WHERE [Showreel_fkId] = @f1"
        Dim fillCommentsCmd As OleDbCommand = New OleDbCommand(fillComments, oleDbConn)
        fillCommentsCmd.CommandType = CommandType.Text
        fillCommentsCmd.Parameters.AddWithValue("@f1", Request.Params("VideoId"))
        Dim dataReader = fillCommentsCmd.ExecuteReader()    'because we are returning results
        GridView3.DataSource = dataReader
        GridView3.DataBind()
        'clear text boxes
        tb_Title.Text = ""
        tb_Comment.Text = ""
        oleDbConn.Close()
        oleDbConn.Dispose()


    End Sub

    Protected Sub btn_Rating_Click(sender As Object, e As EventArgs) Handles btn_Rating.Click



        Dim rateoleDbConn As OleDbConnection = New OleDbConnection(ConfigurationManager.ConnectionStrings("3dBuzzDatabase").ConnectionString)
        rateoleDbConn.Open()

        'find user id
        Dim findIdSqlString As String = "SELECT  aspnet_Users.[UserId] FROM aspnet_Users WHERE aspnet_Users.UserName = @f2"
        Dim findIdCmd As New OleDbCommand(findIdSqlString, rateoleDbConn)
        findIdCmd.Parameters.AddWithValue("@f2", User.Identity.Name)
        findIdCmd.ExecuteNonQuery()
        Dim dataReader As OleDbDataReader = findIdCmd.ExecuteReader()
        Dim storeid As Integer

        dataReader.Read()
        storeid = dataReader.Item("UserId")
        Dim storeida As String = storeid.ToString


        Dim delRatingSql As String = "DELETE FROM UserRating WHERE VideoId_fk = @f1 AND UserId_fk=@f3"
        Dim delRatingCmd As New OleDbCommand(delRatingSql, rateoleDbConn)
        delRatingCmd.Parameters.AddWithValue("@f1", Request.Params("VideoId"))
        delRatingCmd.Parameters.AddWithValue("@f3", storeida)
        delRatingCmd.ExecuteNonQuery()

        Dim addRatingSql As String = "INSERT into UserRating (VideoId_fk, UserId_fk, Rating) VALUES (@f4, @f5, @f6)"
        Dim addRatingCmd As New OleDbCommand(addRatingSql, rateoleDbConn)
        addRatingCmd.Parameters.AddWithValue("@f1", Request.Params("VideoId"))
        addRatingCmd.Parameters.AddWithValue("@f2", storeida)
        addRatingCmd.Parameters.AddWithValue("@f3", Rating1.CurrentRating)
        addRatingCmd.ExecuteNonQuery()

        rateoleDbConn.Close()
        rateoleDbConn.Dispose()
        lbl_Thanks.Text = "Thank you for adding your rating"

    End Sub

    Protected Sub btn_like_Click(sender As Object, e As EventArgs) Handles btn_like.Click
        'find user id

        Dim oleDbConn2 As OleDbConnection = New OleDbConnection(ConfigurationManager.ConnectionStrings("3dBuzzDatabase").ConnectionString)
        oleDbConn2.Open()
        Dim findIdSqlString2 As String = "SELECT  aspnet_Users.[UserId] FROM aspnet_Users WHERE aspnet_Users.UserName = @f10"
        Dim findIdCmd2 As New OleDbCommand(findIdSqlString2, oleDbConn2)
        findIdCmd2.Parameters.AddWithValue("@f10", User.Identity.Name)
        findIdCmd2.ExecuteNonQuery()
        Dim dataReader2 As OleDbDataReader = findIdCmd2.ExecuteReader()
        Dim storeid2 As Integer

        dataReader2.Read()
        storeid2 = dataReader2.Item("UserId")
        Dim storeida2 As String = storeid2.ToString

        Dim addLikeSql As String = "INSERT into Likes (VideoId_fk, UserId_fk) VALUEs (@f20, @f21) "
        Dim addLikeCmd As OleDbCommand = New OleDbCommand(addLikeSql, oleDbConn2)
        addLikeCmd.Parameters.AddWithValue("@f20", Request.Params("VideoId"))
        addLikeCmd.Parameters.AddWithValue("@f21", storeida2)
        addLikeCmd.ExecuteNonQuery()
        btn_like.Visible = False
        lbl_like.Visible = True

        oleDbConn2.Close()
        oleDbConn2.Dispose()


    End Sub

    Protected Sub btn_Tag_Click(sender As Object, e As EventArgs) Handles btn_Tag.Click
        'loop through tags, find which are selected, and update db
        Dim tagoleDbConn As OleDbConnection = New OleDbConnection(ConfigurationManager.ConnectionStrings("3dBuzzDatabase").ConnectionString)
        Dim TagSqlString As String = "INSERT into VideoTagJunction(Video_fkid, Tag_fkid) VALUES (@f40,@f41)"
        tagoleDbConn.Open()

        For Each cb As ListItem In CheckBoxList1.Items

            If cb.Selected Then
                'add the join to to the table of tags and videos
                Dim tagcmd As OleDbCommand = New OleDbCommand(TagSqlString, tagoleDbConn)
                tagcmd.CommandType = CommandType.Text
                tagcmd.Parameters.AddWithValue("@f40", Request.Params("VideoID"))
                tagcmd.Parameters.AddWithValue("@f41", cb.Value)
                tagcmd.ExecuteNonQuery()


            End If
        Next

        If tb_AddTag.Text = "" Then
        Else


            Dim newTags = tb_AddTag.Text.Split(",")   'take contents of textbox and break it into an array of words


            For Each newTag In newTags
                Dim checkTagSql As String = "SELECT * from Tags WHERE Tags = @f50"   'echeck to avoid duplicates


                Dim newTagSql As String = "INSERT into Tags(Tags) VALUES (@f50)"
                Dim newTagcmd As OleDbCommand = New OleDbCommand(newTagSql, tagoleDbConn)
                Dim checkTagcmd As OleDbCommand = New OleDbCommand(checkTagSql, tagoleDbConn)

                checkTagcmd.Parameters.AddWithValue("@f50", newTag)
                'to improve - parse the tag for spaces and remove them before adding
                Dim dataReader = checkTagcmd.ExecuteReader()
                dataReader.Read()
                If dataReader.HasRows = False Then     'the tag does not already exist
                    newTagcmd.CommandType = CommandType.Text
                    newTagcmd.Parameters.AddWithValue("@f50", newTag)
                    newTagcmd.ExecuteNonQuery()
                End If
                dataReader.Close()
                'find last tag added which will be the id of the tag we have just added
                'this will work as we are using Access, single - user, for multi-user then need a transaction based db such as MySql
                'where all the transactions will fail of one of the sequence fails.
                'this robustness is not part of this course, but comprises transaction commands

                Dim getLastIdCmd As OleDbCommand = New OleDbCommand("SELECT TOP 1 ID FROM Tags ORDER BY ID DESC", tagoleDbConn)
                dataReader = getLastIdCmd.ExecuteReader()
                dataReader.Read()
                Dim lastId = dataReader.GetValue(0)   'only 1 value being sent back = ID

                'add this id and the video id in to the junction table


                Dim newTagcmd2 As OleDbCommand = New OleDbCommand(TagSqlString, tagoleDbConn)
                newTagcmd2.Parameters.AddWithValue("@f40", Request.Params("VideoID"))
                newTagcmd2.Parameters.AddWithValue("@f41", lastId)

                newTagcmd2.ExecuteNonQuery()  'create the join
                dataReader.Close()
            Next
        End If

    End Sub
End Class